package com.abc.server;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Properties;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.logging.Level;

import com.abc.shared.ClassifiedItem;


public class DatabaseConnection {
	private Connection connection = null;
    private Statement statement = null;
    private ResultSet results = null;
	private Properties properties=null;
	private String thisOperator="";
	private SystemParams sysparams=null;
	public DatabaseConnection(Properties properties) throws FileNotFoundException, IOException, SQLException, ClassNotFoundException{
		
        this.properties=properties;
        String mysqldatabase= "";
		String mysqlurl = "";
        String username ="";
        String password ="";
        if (properties!=null){
        	mysqldatabase= properties.getProperty("database.name");
    		mysqlurl = "jdbc:mysql://localhost:3306/"+mysqldatabase;
            username = properties.getProperty("database.username");
            password = properties.getProperty("database.password");
        }
        else{
        	 mysqldatabase= "classified";
     		 mysqlurl = "jdbc:mysql://localhost:3306/"+mysqldatabase;
             username = "root";
             password = "abc123";
        }
       
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(mysqlurl, username, password);
    	statement = connection.createStatement();
    	sysparams=getSystemParameters();
    	thisOperator=sysparams.getThisOperator();
   
        	
	}
	
	public ResultSet sqlSelect(String querystatement) throws SQLException{
		
		return statement.executeQuery(querystatement);

	}
	
	public SystemParams getSystemParameters(){
		return new SystemParams(this);
	}
	
	public Boolean sqlUpdate(String querystatement) throws SQLException{
		
		return statement.execute(querystatement);

	}
	
	public void close() throws SQLException{
		
		if (results!=null)results.close();
		if (statement!=null)statement.close();
		if (connection!=null)connection.close();
		
		
	}

	public void purgeSubscriptions(int daysafterexpiry) throws SQLException{
		sqlUpdate("delete from `subscriptions`  where `expiredate`<DATE_SUB(CURDATE(), INTERVAL "+daysafterexpiry+" DAY)");
	}
	
	public LinkedHashMap<Integer, String> getClassifiedItemByParent(int parentid) throws SQLException{		
		LinkedHashMap<Integer, String> classific =  new LinkedHashMap<Integer, String>();
		results = statement.executeQuery("SELECT `itemid`,`name` FROM `classification` where `parentid`="+parentid+" order by name ");
	     
	    while (results.next()) {
	    	classific.put(results.getInt("itemid"), results.getString("name"));
	    																																									
	    }
	   
	    return classific;
	}
	
	public ClassifiedItem getClassifiedItemByID(int itemid) throws SQLException{		
		results = statement.executeQuery("SELECT * FROM `classification` where `itemid`="+itemid);
	     
	    results.next();
	    return new ClassifiedItem(results.getInt("itemid"), results.getInt("parentid"), results.getString("name"), results.getString("prompt"), results.getInt("rank"), results.getString("keywords"), results.getString("servicetype"), results.getInt("level"),results.getString("smstext"), results.getDouble("lngtude_maxprice"),results.getDouble("lattude_minprice"));
	   
	}
	
	public void deleteClassifiedItemByID(int itemid) throws SQLException{		
		sqlUpdate("delete FROM `classification` where `itemid`="+itemid);
	     
	    
	}
	
	
	
	public void purgeAlerts(int daysafterexpiry) throws SQLException{
		sqlUpdate("delete from `alerts`  where `expdate`<DATE_SUB(CURDATE(), INTERVAL "+daysafterexpiry+" DAY) and `operator`='"+sysparams.getThisOperator()+"'");
		sqlUpdate("delete from `alerts`  where `expdate`<CURDATE() and `operator`<>'"+sysparams.getThisOperator()+"'");
	}
}
